﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Text;
using StoreManagementSystem.DomainObjects;
using System.Data;

namespace StoreManagementSystem.DataAccessLayer
{
    public class SQLCategoryDAO : IDataAccessObject<Entity>
    {

        private ISQLDataProvider DataProvider { get; set; }
        public SQLCategoryDAO(ISQLDataProvider SQLDataProvider)
        {
            DataProvider = SQLDataProvider;
        }

        public void create(Entity e)
        {
            if (e is Category)
            {
                Category cat = (Category) e;
                List<SQLParam> dbparams = new List<SQLParam>();
                dbparams.Add(new SQLParam("?id", cat.id));
                dbparams.Add(new SQLParam("?name", cat.Name));

                // build the parameter string to pass in values
                StringBuilder paramstr = new StringBuilder();
                for (int i = 0; i < dbparams.Count - 1; i++)
                {
                    paramstr.Append(dbparams[i].Name + ",");
                }
                paramstr.Append(dbparams[dbparams.Count - 1].Name);

                string SQL = "INSERT INTO category (id,Name)" +
                        "VALUES(" + paramstr.ToString() + ");";

                DataProvider.Open();
                DataProvider.ExecuteSQL(SQL, dbparams, false);
                DataProvider.Close();
                var products = cat.ProductList;

                foreach (var product in products) 
                {
                    insertProduct(cat.Name, product.Name);
                }
            }
        }

        public Entity get(params string[] parameters)
        {
            List<SQLParam> dbparams = new List<SQLParam>();
            string SQL = "";
            string Name = "";

            Name = parameters[0];
            dbparams.Add(new SQLParam("?Name", Name));

            SQL = "SELECT * FROM category WHERE Name=?Name";
            DataProvider.Open();
            DataTable dt = DataProvider.ExecuteSQL(SQL, dbparams, true);
            DataProvider.Close();
            Category cat = new Category();
            cat.id = dt.Rows[0]["id"].ToString();
            cat.Name = dt.Rows[0]["Name"].ToString();
            return cat;
        }

        /// <summary>
        /// Get a category by id
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public Category getById(string id)
        {
            List<SQLParam> dbparams = new List<SQLParam>();
            string SQL = "";

            dbparams.Add(new SQLParam("?id", id));

            SQL = "SELECT * FROM category WHERE id=?id";
            DataProvider.Open();
            DataTable dt = DataProvider.ExecuteSQL(SQL, dbparams, true);
            DataProvider.Close();
            if (dt.Rows.Count == 0)
                return null;
            Category cat = new Category();
            cat.id = dt.Rows[0]["id"].ToString();
            cat.Name = dt.Rows[0]["Name"].ToString();
            return cat;
        }

        public void remove(Entity e)
        {
            throw new NotImplementedException();
        }

        public void update(Entity e)
        {
            throw new NotImplementedException();
        }

        private void insertProduct(string product, string category)
        {
            List<SQLParam> dbparams = new List<SQLParam>();
            dbparams.Add(new SQLParam("?product", product));
            dbparams.Add(new SQLParam("?category", category));

            // build the parameter string to pass in values
            StringBuilder paramstr = new StringBuilder();
            for (int i = 0; i < dbparams.Count - 1; i++)
            {
                paramstr.Append(dbparams[i].Name + ",");
            }
            paramstr.Append(dbparams[dbparams.Count - 1].Name);

            string SQL = "INSERT INTO productcategory (product,category)" +
                    "VALUES(" + paramstr.ToString() + ");";

            DataProvider.Open();
            DataProvider.ExecuteSQL(SQL, dbparams, false);
            DataProvider.Close();

        }

        private void getProduct(Category cat)
        {
            var factory = new SQLDAOFactory(this.DataProvider);
            var productDAO = factory.createDAO("product");

            List<SQLParam> dbparams = new List<SQLParam>();
            string SQL = "";
            string Name = "";

            Name = cat.Name;
            dbparams.Add(new SQLParam("?Name", Name));

            SQL = "SELECT * FROM productcategory WHERE Name=?Name";
            DataProvider.Open();
            DataTable dt = DataProvider.ExecuteSQL(SQL, dbparams, true);
            DataProvider.Close();

            foreach (DataRow row in dt.Rows)
            {
                cat.addProdduct(productDAO.get(row["Product"].ToString()) as Product);
            }
        }

    }
}